package com.apes.fn.scm.retailOrder.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.scm.retailOrder.model.BusinessOrderType;
import com.apes.fn.scm.retailOrder.model.BusinessType;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;

/**
 * @BelongsProject: apes-server
 * @BelongsPackage: com.apes.fn.scm.retailOrder.repository
 * @ClassName: TurnoverDao
 * @Author: pyb
 * @Description: 营业额Dao层
 * @CreateTime: 2021-06-17 10:14
 * @Version: 1.0
 */
@Component
public class TurnoverDao extends ApesDao {
    static Map<String, Function<StringBuffer,String>> orderMap=new HashMap<>();
    {
        orderMap.put(BusinessOrderType.RetailOrder.getOrderType(), this::retailOrderSql);
        orderMap.put(BusinessOrderType.RetailOrder.getOrderType()+"PayMethod", this::retailOrderPayMethodSql);

        orderMap.put(BusinessOrderType.SaleOrder.getOrderType(), this::saleOrderSql);
        orderMap.put(BusinessOrderType.SaleOrder.getOrderType()+"PayMethod", this::notPayMethodSql);

        orderMap.put(BusinessOrderType.SaleReturn.getOrderType(), this::saleOrderReturnSql);
        orderMap.put(BusinessOrderType.SaleReturn.     getOrderType()+"PayMethod", this::notPayMethodSql);

        orderMap.put(BusinessOrderType.Voucher.getOrderType(), this::voucherReturnSql);
        orderMap.put(BusinessOrderType.Voucher.getOrderType()+"PayMethod", this::notPayMethodSql);

        orderMap.put(BusinessOrderType.IntroductionFee.getOrderType(), this::introductionFeeSql);
        orderMap.put(BusinessOrderType.IntroductionFee.getOrderType()+"PayMethod", this::notPayMethodSql);

        orderMap.put(BusinessOrderType.UsedCar.getOrderType(), this::usedCarSql);
        orderMap.put(BusinessOrderType.UsedCar.getOrderType()+"PayMethod", this::notPayMethodSql);

        orderMap.put(BusinessOrderType.Cross.getOrderType(), this::crossSql);
        orderMap.put(BusinessOrderType.Cross.getOrderType()+"PayMethod", this::notPayMethodSql);

        orderMap.put(BusinessOrderType.Deposit.getOrderType(), this::depositSql);
        orderMap.put(BusinessOrderType.Deposit.getOrderType()+"PayMethod", this::depositPayMethodSql);

        orderMap.put(BusinessOrderType.FundIncome.getOrderType(), this::fundIncomeSql);
        orderMap.put(BusinessOrderType.FundIncome.getOrderType()+"PayMethod", this::fundIncomePayMethodSql);
    }



    /**
     * 查找单据明细
     *
     * @param param
     * @return
     */
    public JSONArray findOrder(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        String sql = orderMap.get(BusinessOrderType.getBusinessOrderTypeByOrderType(param.getString("INNER_BUSINESS_TYPE")).getOrderType()).apply(stringBuffer);
        return toJSONArray(find(sql, param));
    }

    /**
     * 查找单据明细（收款方式）
     *
     * @param param
     * @return
     */
    public JSONArray findOrderPayMethod(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        String sql = orderMap.get(BusinessOrderType.getBusinessOrderTypeByOrderType(param.getString("INNER_BUSINESS_TYPE")).getOrderType()+"PayMethod").apply(stringBuffer);
        if (Objects.isNull(sql)) {
            return new JSONArray();
        }
        return toJSONArray(find(sql, param));
    }



    /**
     * 分页查找服务业绩明细
     *
     * @param param
     * @return
     */
    public JSONArray findServicePerformance(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        servicePerformanceSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找服务业绩明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findServicePerformance_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        servicePerformanceSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找服务业绩收款方式汇总
     *
     * @param param
     * @return
     */
    public JSONArray findServicePerformance_summary(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        servicePerformanceSummarySql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查找服务业绩月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findServicePerformanceMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        servicePerformanceMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 分页查找年卡销售明细
     *
     * @param param
     * @return
     */
    public JSONArray findCardSale(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        cardSaleSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找年卡销售明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findCardSale_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        cardSaleSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找年卡销售收款方式汇总
     *
     * @param param
     * @return
     */
    public JSONArray findCardSale_summary(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        cardSaleSummarySql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查找年卡销售月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findCardSaleMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        cardSaleMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 分页查找储值明细
     *
     * @param param
     * @return
     */
    public JSONArray findStoredValue(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        storedValueSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找储值明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findStoredValue_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        storedValueSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";
        return toJSONArray(find(sql, param));
    }

    /**
     * 查找储值收款方式汇总
     *
     * @param param
     * @return
     */
    public JSONArray findStoredValue_summary(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        storedValueSummarySql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查找储值月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findStoredValueMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        storedValueMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 分页查找订金明细
     *
     * @param param
     * @return
     */
    public JSONArray findDeposit(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        depositSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找订金明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findDeposit_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        depositSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";
        return toJSONArray(find(sql, param));
    }

    /**
     * 查找订金收款方式汇总
     *
     * @param param
     * @return
     */
    public JSONArray findDeposit_summary(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        depositSummarySql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查找订金月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findDepositMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        depositMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 分页查找资金收入明细
     *
     * @param param
     * @return
     */
    public JSONArray findFundIncome(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        fundIncomeSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找资金收入明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findFundIncome_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        fundIncomeSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";
        return toJSONArray(find(sql, param));
    }

    /**
     * 查找资金收入收款方式汇总
     *
     * @param param
     * @return
     */
    public JSONArray findFundIncome_summary(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        fundIncomeSummarySql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查找资金收入月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findFundIncomeMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        fundIncomeMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 分页查找接案介绍费明细
     *
     * @param param
     * @return
     */
    public JSONArray findIntroductionFee(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        introductionFeeSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找接案介绍费明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findIntroductionFee_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        introductionFeeSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";
        return toJSONArray(find(sql, param));
    }
    /**
     * 查找接案介绍费月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findIntroductionFeeMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        introductionFeeMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 分页查找二手车佣金明细
     *
     * @param param
     * @return
     */
    public JSONArray findUsedCar(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();

        usedCarSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找二手车佣金明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findUsedCar_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        usedCarSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";
        return toJSONArray(find(sql, param));
    }
    /**
     * 查找二手车佣金月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findUsedCarMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        usedCarMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 分页查找跨公司结算明细
     *
     * @param param
     * @return
     */
    public JSONArray findCross(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        crossSql(param, stringBuffer);
        String sql = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                stringBuffer.toString()+
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";

        return toJSONArray(find(sql, param));
    }

    /**
     * 查找跨公司结算明细总数
     *
     * @param param
     * @return
     */
    public JSONArray findCross_count(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        crossSql(param, stringBuffer);
        String sql = "SELECT COUNT(1) COUNT ,SUM(TOTAL) SUM FROM ( " +
                stringBuffer.toString() +
                " ) ";
        return toJSONArray(find(sql, param));
    }
    /**
     * 查找跨公司结算月汇总
     *
     * @param param
     * @return
     */
    public JSONArray findCrossMonth(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        crossMonthSql(param, stringBuffer);
        return toJSONArray(find(stringBuffer.toString(), param));
    }




    /**
     * 服务业绩sql
     *
     * @param param
     * @param stringBuffer
     */
    public void servicePerformanceSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL - B.DISCOUNT_AMOUNT)        TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE , 'YYYY-MM-DD HH24:MI:SS')           SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM RETAIL_ORDER_ITEM B,\n" +
                "     RETAIL_ORDER C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.SALE_ID = C.ID\n" +
                "  AND C.CLAIM_TYPE IS NULL\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID)) in ('001', '002', '003', '021', '004', '007', '005', '008', '006', '009', '024', '020')\n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");
        stringBuffer.append("  UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL)        TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE , 'YYYY-MM-DD HH24:MI:SS')           SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM sale_order_ITEM B,\n" +
                "     sale_order C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.SALE_ID = C.ID\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID))" +
                " in ('001', '002', '003', '021', '004', '007', '005', '008', '006', '009', '024', '020')\n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");

        stringBuffer.append(" UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL)        TOTAL,\n" +
                "      TO_CHAR(C.DATE_APPROVE , 'YYYY-MM-DD HH24:MI:SS')           SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM SALE_RETURN_ITEM B,\n" +
                "     SALE_RETURN C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.SALE_RETURN_ID = C.ID\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID)) in ('001', '002', '003', '021', '004', '007', '005', '008', '006', '009', '024', '020')\n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");
        stringBuffer.append(" UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL)        TOTAL,\n" +
                "       TO_CHAR(C.CREATE_DATE , 'YYYY-MM-DD HH24:MI:SS')            SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM ACC_VOUCHER_ITEM B,\n" +
                "     ACC_VOUCHER C,\n" +
                "     ACC_VOUCHER_EXTENSION D\n" +
                "WHERE B.VOUCHER_ID = C.ID\n" +
                "  AND D.ID = B.ID\n" +
                "  AND C.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "  AND C.SCENE_ID = 'SR004'\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID)) in ('001', '002', '003', '021', '004', '007', '005', '008', '006', '009', '024', '020')\n" +
                "  AND trunc(C.CREATE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.CREATE_DATE, C.CLERK_ID  ");
    }
    /**
     * 服务业绩收款方式汇总sql
     *
     * @param param
     * @param stringBuffer
     */
    public void servicePerformanceSummarySql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("select SUM(TOTAL) TOTAL, PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME\n" +
                "from (\n" +
                "         SELECT SUM(NVL(B.AMOUNT, 0))                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "         FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "              (SELECT distinct C.ID, C.DEPT_ID\n" +
                "               FROM RETAIL_ORDER_ITEM B,\n" +
                "                    RETAIL_ORDER C\n" +
                "               WHERE B.SALE_ID = C.ID\n" +
                "                 AND C.CLAIM_TYPE IS NULL\n" +
                "                 AND (SELECT S.SERVE_TYPE_ID\n" +
                "                      FROM MAS_SERVE_CATEGORY S\n" +
                "                      WHERE S.CLASSIFY_ID =\n" +
                "                            (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID = B.PRODUCT_ID)) in\n" +
                "                     ('001', '002', '003',\n" +
                "                      '021', '004', '007', '005',\n" +
                "                      '008', '006', '009', '024',\n" +
                "                      '020')\n" +
                "                 AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("               ) C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "         GROUP BY B.PAYMENT_METHOD_TYPE");
        stringBuffer.append("    UNION ALL\n" +
                "         SELECT SUM(NVL(B.PRICE_TOTAL, 0))\n" +
                "                       TOTAL,\n" +
                "                '09'   PAYMENT_METHOD_TYPE,\n" +
                "                '其他应收' PAYMENT_METHOD_NAME\n" +
                "         FROM sale_order_ITEM B,\n" +
                "              sale_order C\n" +
                "         WHERE B.SALE_ID = C.ID\n" +
                "           AND (SELECT S.SERVE_TYPE_ID\n" +
                "                FROM MAS_SERVE_CATEGORY S\n" +
                "                WHERE S.CLASSIFY_ID = (select P.CLASSIFY_ID\n" +
                "                                       FROM MAS_PRODUCT P\n" +
                "                                       WHERE P.ID = B.PRODUCT_ID)) in ('001', '002', '003', '021', '004', '007',\n" +
                "                                                                       '005', '008', '006', '009', '024', '020')\n" +
                "           AND trunc(C.DATE_APPROVE) =  :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }

        stringBuffer.append("  UNION ALL\n" +
                "         SELECT SUM(NVL(B.PRICE_TOTAL, 0)) TOTAL,\n" +
                "                '09'                       PAYMENT_METHOD_TYPE,\n" +
                "                '其他应收'                     PAYMENT_METHOD_NAME\n" +
                "         FROM SALE_RETURN_ITEM B,\n" +
                "              SALE_RETURN C\n" +
                "         WHERE B.SALE_RETURN_ID = C.ID\n" +
                "           AND (SELECT S.SERVE_TYPE_ID\n" +
                "                FROM MAS_SERVE_CATEGORY S\n" +
                "                WHERE S.CLASSIFY_ID = (select P.CLASSIFY_ID\n" +
                "                                       FROM MAS_PRODUCT P\n" +
                "                                       WHERE P.ID = B.PRODUCT_ID)) in\n" +
                "               ('001', '002', '003', '021', '004', '007', '005', '008', '006', '009',\n" +
                "                '024', '020')\n" +
                "           AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("   UNION ALL\n" +
                "         SELECT SUM(NVL(B.PRICE_TOTAL, 0)) TOTAL,\n" +
                "                '09'                       PAYMENT_METHOD_TYPE,\n" +
                "                '其他应收'                     PAYMENT_METHOD_NAME\n" +
                "         FROM ACC_VOUCHER_ITEM B,\n" +
                "              ACC_VOUCHER C\n" +
                "         WHERE B.VOUCHER_ID = C.ID\n" +
                "           AND C.VOUCHER_TYPE\n" +
                "             = 'CUSTOMER_SALE'\n" +
                "           AND C.SCENE_ID = 'SR004'\n" +
                "           AND (SELECT S.SERVE_TYPE_ID\n" +
                "                FROM MAS_SERVE_CATEGORY S\n" +
                "                WHERE S.CLASSIFY_ID = (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID = B.PRODUCT_ID)) in\n" +
                "               ('001', '002', '003', '021', '004', '007', '005', '008', '006', '009', '024', '020')\n" +
                "           AND trunc(C.CREATE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  )group by PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME  ");
    }


    /**
     * 服务业绩月SQL
     * @param param
     * @param stringBuffer
     */
    public void servicePerformanceMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(SERVICE_PERFORMANCE) - SUM(YEARCARD) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }

    /**
     * 年卡销售sql
     *
     * @param param
     * @param stringBuffer
     */
    public void cardSaleSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL - B.DISCOUNT_AMOUNT)        TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE , 'YYYY-MM-DD HH24:MI:SS')           SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM RETAIL_ORDER_ITEM B,\n" +
                "     RETAIL_ORDER C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.SALE_ID = C.ID\n" +
                "  AND C.CLAIM_TYPE IS NULL\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID)) ='010'\n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");
        stringBuffer.append("  UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL)        TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE , 'YYYY-MM-DD HH24:MI:SS')           SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM sale_order_ITEM B,\n" +
                "     sale_order C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.SALE_ID = C.ID\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID))='010' \n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");

        stringBuffer.append(" UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL)        TOTAL,\n" +
                "      TO_CHAR(C.DATE_APPROVE , 'YYYY-MM-DD HH24:MI:SS')           SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM SALE_RETURN_ITEM B,\n" +
                "     SALE_RETURN C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                   ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.SALE_RETURN_ID = C.ID\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID)) ='010'\n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");
        stringBuffer.append(" UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL)        TOTAL,\n" +
                "       TO_CHAR(C.CREATE_DATE , 'YYYY-MM-DD HH24:MI:SS')            SETTLE_DATE,\n" +
                "       (CASE\n" +
                "           WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "           ELSE\n" +
                "               (SELECT P.NAME\n" +
                "                FROM MAS_PARTY P,\n" +
                "                     MAS_PARTY_ROLE R\n" +
                "                WHERE P.ID = R.PARTY_ID\n" +
                "                  AND R.ID = C.CLERK_ID) END) SETTLE_PERSON\n" +
                "FROM ACC_VOUCHER_ITEM B,\n" +
                "     ACC_VOUCHER C,\n" +
                "     ACC_VOUCHER_EXTENSION D\n" +
                "WHERE B.VOUCHER_ID = C.ID\n" +
                "  AND D.ID = B.ID\n" +
                "  AND C.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "  AND C.SCENE_ID = 'SR004'\n" +
                "  AND (SELECT S.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY S WHERE S.CLASSIFY_ID= (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID=B.PRODUCT_ID)) ='010'\n" +
                "  AND trunc(C.CREATE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.CREATE_DATE, C.CLERK_ID  ");
    }
    /**
     * 年卡销售收款方式汇总sql
     *
     * @param param
     * @param stringBuffer
     */
    public void cardSaleSummarySql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("select SUM(TOTAL) TOTAL, PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME\n" +
                "from (\n" +
                "         SELECT SUM(NVL(B.AMOUNT, 0))                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "         FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "              (SELECT distinct C.ID, C.DEPT_ID\n" +
                "               FROM RETAIL_ORDER_ITEM B,\n" +
                "                    RETAIL_ORDER C\n" +
                "               WHERE B.SALE_ID = C.ID\n" +
                "                 AND C.CLAIM_TYPE IS NULL\n" +
                "                 AND (SELECT S.SERVE_TYPE_ID\n" +
                "                      FROM MAS_SERVE_CATEGORY S\n" +
                "                      WHERE S.CLASSIFY_ID =\n" +
                "                            (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID = B.PRODUCT_ID))='010' \n" +
                "                 AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("               ) C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "         GROUP BY B.PAYMENT_METHOD_TYPE");
        stringBuffer.append("    UNION ALL\n" +
                "         SELECT SUM(NVL(B.PRICE_TOTAL, 0))\n" +
                "                       TOTAL,\n" +
                "                '09'   PAYMENT_METHOD_TYPE,\n" +
                "                '其他应收' PAYMENT_METHOD_NAME\n" +
                "         FROM sale_order_ITEM B,\n" +
                "              sale_order C\n" +
                "         WHERE B.SALE_ID = C.ID\n" +
                "           AND (SELECT S.SERVE_TYPE_ID\n" +
                "                FROM MAS_SERVE_CATEGORY S\n" +
                "                WHERE S.CLASSIFY_ID = (select P.CLASSIFY_ID\n" +
                "                                       FROM MAS_PRODUCT P\n" +
                "                                       WHERE P.ID = B.PRODUCT_ID))='010'\n" +
                "           AND trunc(C.DATE_APPROVE) =  :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }

        stringBuffer.append("  UNION ALL\n" +
                "         SELECT SUM(NVL(B.PRICE_TOTAL, 0)) TOTAL,\n" +
                "                '09'                       PAYMENT_METHOD_TYPE,\n" +
                "                '其他应收'                     PAYMENT_METHOD_NAME\n" +
                "         FROM SALE_RETURN_ITEM B,\n" +
                "              SALE_RETURN C\n" +
                "         WHERE B.SALE_RETURN_ID = C.ID\n" +
                "           AND (SELECT S.SERVE_TYPE_ID\n" +
                "                FROM MAS_SERVE_CATEGORY S\n" +
                "                WHERE S.CLASSIFY_ID = (select P.CLASSIFY_ID\n" +
                "                                       FROM MAS_PRODUCT P\n" +
                "                                       WHERE P.ID = B.PRODUCT_ID)) ='010'\n" +
                "           AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("   UNION ALL\n" +
                "         SELECT SUM(NVL(B.PRICE_TOTAL, 0)) TOTAL,\n" +
                "                '09'                       PAYMENT_METHOD_TYPE,\n" +
                "                '其他应收'                     PAYMENT_METHOD_NAME\n" +
                "         FROM ACC_VOUCHER_ITEM B,\n" +
                "              ACC_VOUCHER C\n" +
                "         WHERE B.VOUCHER_ID = C.ID\n" +
                "           AND C.VOUCHER_TYPE\n" +
                "             = 'CUSTOMER_SALE'\n" +
                "           AND C.SCENE_ID = 'SR004'\n" +
                "           AND (SELECT S.SERVE_TYPE_ID\n" +
                "                FROM MAS_SERVE_CATEGORY S\n" +
                "                WHERE S.CLASSIFY_ID = (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID = B.PRODUCT_ID))='010'\n" +
                "           AND trunc(C.CREATE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  )group by PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME  ");
    }


    /**
     * 年卡销售月SQL
     * @param param
     * @param stringBuffer
     */
    public void cardSaleMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(BUY_YEARCARD) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }

    /**
     * 储值sql
     *
     * @param param
     * @param stringBuffer
     */
    public void storedValueSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.PRICE_TOTAL - B.DISCOUNT_AMOUNT)                               TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = C.CLERK_ID) END)          SETTLE_PERSON\n" +
                "FROM RETAIL_ORDER_ITEM B,\n" +
                "     RETAIL_ORDER C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE c.id = B.sale_id\n" +
                "  and (SELECT sc.SERVE_TYPE_ID\n" +
                "       FROM MAS_SERVE_CATEGORY sc\n" +
                "       WHERE sc.classify_id =\n" +
                "             (SELECT t.classify_id\n" +
                "              FROM MAS_PRODUCT t\n" +
                "              WHERE t.id = B.product_id)) = '012'" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");
        stringBuffer.append("  UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.AMOUNT)*-1                                    TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = C.CLERK_ID) END)          SETTLE_PERSON\n" +
                "FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "     RETAIL_ORDER C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "  AND B.PAYMENT_METHOD_TYPE IN ('05')  \n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");

        stringBuffer.append("  UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.AMOUNT) * -1                               TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.CREATOR_ID = '00002256' OR C.CREATOR_ID IS NULL THEN NULL\n" +
                "            ELSE (SELECT P.NAME\n" +
                "                  FROM MAS_PARTY P,\n" +
                "                       MAS_PARTY_ROLE R\n" +
                "                  WHERE P.ID = R.PARTY_ID\n" +
                "                    AND R.ID = C.CREATOR_ID) END)       SETTLE_PERSON\n" +
                "FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "     ADVANCE_PAYMENT C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID\n" +
                "         = D.ID\n" +
                "WHERE B.PAYMENT_ID = C.ID\n" +
                "  AND B.PAYMENT_METHOD_TYPE IN ('05')\n" +
                "  AND trunc(C.DATE_APPROVE)\n" +
                "    = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CREATOR_ID   ");

    }
    /**
     * 储值收款方式汇总sql
     *
     * @param param
     * @param stringBuffer
     */
    public void storedValueSummarySql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("select SUM(TOTAL) TOTAL, PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME\n" +
                "from (\n" +
                "         SELECT SUM(NVL(B.AMOUNT, 0))                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "         FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "              (SELECT distinct C.ID, C.DEPT_ID\n" +
                "               FROM RETAIL_ORDER_ITEM B,\n" +
                "                    RETAIL_ORDER C\n" +
                "               WHERE B.SALE_ID = C.ID\n" +
                "                 AND C.CLAIM_TYPE IS NULL\n" +
                "                 AND (SELECT S.SERVE_TYPE_ID\n" +
                "                      FROM MAS_SERVE_CATEGORY S\n" +
                "                      WHERE S.CLASSIFY_ID =\n" +
                "                            (select P.CLASSIFY_ID FROM MAS_PRODUCT P WHERE P.ID = B.PRODUCT_ID))='012' \n" +
                "                 AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("               ) C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "         GROUP BY B.PAYMENT_METHOD_TYPE");
        stringBuffer.append("    UNION ALL\n" +
                "        SELECT SUM(NVL(B.AMOUNT, 0))*-1                                                        TOTAL,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "from RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "     RETAIL_ORDER C\n" +
                "WHERE B.RETAIL_ORDER_ID = C.ID AND B.PAYMENT_METHOD_TYPE IN ('05') \n" +
                "           AND trunc(C.DATE_APPROVE) =  :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("   GROUP BY B.PAYMENT_METHOD_TYPE  ");
        stringBuffer.append("  )group by PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME  ");

        stringBuffer.append("UNION ALL " +
                " select SUM(TOTAL) TOTAL, PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME\n" +
                "from (");

        stringBuffer.append("    \n" +
                "        SELECT SUM(NVL(B.AMOUNT, 0)) * -1           TOTAL,\n" +
                "             PAYMENT_METHOD_TYPE,\n" +
                "             (SELECT M.NAME\n" +
                "              FROM MAS_PAYMENT_METHOD M\n" +
                "              WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "      from ADVANCE_PAYMENT_METHOD B,\n" +
                "           ADVANCE_PAYMENT C\n" +
                "      WHERE B.PAYMENT_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('05')\n" +
                "        AND trunc(C.DATE_APPROVE)\n" +
                "          =  :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("   GROUP BY B.PAYMENT_METHOD_TYPE  ");
        stringBuffer.append("  )group by PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME  ");
    }


    /**
     * 储值月SQL
     * @param param
     * @param stringBuffer
     */
    public void storedValueMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(STORED_VALUE_RECHARGE -STORED_VALUE) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }

    /**
     * 订金sql
     *
     * @param param
     * @param stringBuffer
     */
    public void depositSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.AMOUNT)                                    TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.APPROVE_UID = '00002256' OR C.APPROVE_UID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = C.APPROVE_UID) END)          SETTLE_PERSON\n" +
                "               FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "              WHERE B.PAYMENT_ID = C.ID\n" +
                "                AND C.STATE = 'approve' " +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.APPROVE_UID   ");
        stringBuffer.append(" UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.AMOUNT)*-1                                    TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.CLERK_ID = '00002256' OR C.CLERK_ID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = C.CLERK_ID) END)          SETTLE_PERSON\n" +
                "FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "     RETAIL_ORDER C LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "  AND B.PAYMENT_METHOD_TYPE IN ('12')   \n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.CLERK_ID   ");

        stringBuffer.append(" UNION ALL\n" +
                "SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(B.AMOUNT)*-1                                    TOTAL,\n" +
                "       TO_CHAR(C.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.APPROVE_UID = '00002256' OR C.APPROVE_UID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = C.APPROVE_UID) END)          SETTLE_PERSON\n" +
                "FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "     ADVANCE_PAYMENT C LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE B.payment_id = C.ID\n" +
                "  AND C.STATE = 'approve'\n" +
                "  AND B.PAYMENT_METHOD_TYPE IN ('12')   \n" +
                "  AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.DATE_APPROVE, C.APPROVE_UID   ");

    }
    /**
     * 订金收款方式汇总sql
     *
     * @param param
     * @param stringBuffer
     */
    public void depositSummarySql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("select SUM(TOTAL) TOTAL, PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME\n" +
                "                from (SELECT SUM(NVL(B.AMOUNT, 0))                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "               FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "              WHERE B.PAYMENT_ID = C.ID\n" +
                "                AND C.STATE = 'approve'  \n" +
                "                 AND trunc(C.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY B.PAYMENT_METHOD_TYPE");
        stringBuffer.append("    UNION ALL\n" +
                "      SELECT SUM(NVL(B.AMOUNT, 0))*-1                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "     RETAIL_ORDER C\n" +
                "WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "  AND B.PAYMENT_METHOD_TYPE IN ('12')\n" +
                "           AND trunc(C.DATE_APPROVE) =  :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("   GROUP BY B.PAYMENT_METHOD_TYPE  ");

        stringBuffer.append("    UNION ALL\n" +
                "      SELECT SUM(NVL(B.AMOUNT, 0))*-1                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "               FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "              WHERE B.PAYMENT_ID = C.ID\n" +
                "  AND B.PAYMENT_METHOD_TYPE IN ('12')\n" +
                "           AND trunc(C.DATE_APPROVE) =  :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("   GROUP BY B.PAYMENT_METHOD_TYPE  ");


        stringBuffer.append("  )group by PAYMENT_METHOD_TYPE, PAYMENT_METHOD_NAME  ");
    }


    /**
     * 订金月SQL
     * @param param
     * @param stringBuffer
     */
    public void depositMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(ADVANCE_PAYMENT_RECHARGE -ADVANCE_PAYMENT) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }


    /**
     * 资金收入sql
     *
     * @param param
     * @param stringBuffer
     */
    public void fundIncomeSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT C.ID,\n" +
                "       C.INNER_BUSINESS_TYPE,\n" +
                "       D.CAR_LICENSE,\n" +
                "       SUM(NVL(E.INCOME_AMOUNT, 0))                     TOTAL,\n" +
                "       TO_CHAR(C.APPROVE_DATE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN C.APPROVE_UID = '00002256' OR C.APPROVE_UID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = C.APPROVE_UID) END)       SETTLE_PERSON\n" +
                "FROM MONEY_INCOME_ITEM E,\n" +
                "     (SELECT B.ID ID, B.DEPT_ID, B.APPROVE_DATE, B.INNER_BUSINESS_TYPE, B.APPROVE_UID, B.CUSTOMER_EXPAND_ID\n" +
                "      FROM MAS_DEPT A,\n" +
                "           MONEY_INCOME B\n" +
                "      WHERE A.COMPANY_ID = B.COMPANY_ID\n" +
                "        AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                "        AND A.DEPT_TYPE = '000007'\n" +
                "        AND B.STATE = 'approve') C\n" +
                "         LEFT JOIN FN_CUSTOMER_EXPAND D ON C.CUSTOMER_EXPAND_ID = D.ID\n" +
                "WHERE C.ID = E.MONEY_INCOME " +
                "  AND trunc(C.APPROVE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append("  GROUP BY C.ID, C.INNER_BUSINESS_TYPE, D.CAR_LICENSE, C.APPROVE_DATE, C.APPROVE_UID   ");
    }
    /**
     * 资金收入收款方式汇总sql
     *
     * @param param
     * @param stringBuffer
     */
    public void fundIncomeSummarySql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("   SELECT SUM(NVL(E.INCOME_AMOUNT, 0))                                                        TOTAL,\n" +
                "                PAYMENT_METHOD_TYPE,\n" +
                "                (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = E.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "      FROM (SELECT  B.ID ID,B.DEPT_ID, B.APPROVE_DATE\n" +
                "               FROM MAS_DEPT A, MONEY_INCOME B\n" +
                "              WHERE A.COMPANY_ID = B.COMPANY_ID\n" +
                "                AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                "                AND A.DEPT_TYPE ='000007'\n" +
                "                AND B.STATE = 'approve') C, MONEY_INCOME_ITEM E\n" +
                "     WHERE C.ID = E.MONEY_INCOME \n" +
                "                 AND trunc(C.APPROVE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND C.DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY E.PAYMENT_METHOD_TYPE");
    }


    /**
     * 资金收入月SQL
     * @param param
     * @param stringBuffer
     */
    public void fundIncomeMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(CAPITAL_INCOME) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }

    /**
     * 接案介绍费sql
     *
     * @param param
     * @param stringBuffer
     */
    public void introductionFeeSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT A.ID,\n" +
                "       'IntroductionFee'                               INNER_BUSINESS_TYPE,\n" +
                "       A.CAR_LICENSE,\n" +
                "       NVL(A.AMOUNT, 0)                           TOTAL,\n" +
                "       TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "        case when a.EXPENSE_TYPE='infoExpense' then '接案介绍费'\n" +
                "                     when a.EXPENSE_TYPE='platformExpense' then '平台管理费' else '定损管理费' end as EXPENSE_TYPE,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) SETTLE_DEPT_NAME," +
                "       null                                            SETTLE_PERSON\n" +
                "FROM FN_SETTLE_EXPENSE A\n" +
                "WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "  AND A.TYPE = 'in' " +
                "  AND trunc(A.CREATE_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND A.DEPT_ID=:DEPT_ID");
        }
    }

    /**
     * 接案介绍费月SQL
     * @param param
     * @param stringBuffer
     */
    public void introductionFeeMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(FN_SETTLE_EXPENS) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }

    /**
     * 二手车佣金sql
     *
     * @param param
     * @param stringBuffer
     */
    public void usedCarSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT A.ID,\n" +
                "       A.INNER_BUSINESS_TYPE,\n" +
                "      A.LICENSE_PLATE CAR_LICENSE,\n" +
                "       NVL(A.STORE_PRICE, 0)                     TOTAL,\n" +
                "       TO_CHAR(A.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (CASE\n" +
                "            WHEN A.FOLLOW_ID = '00002256' OR A.FOLLOW_ID IS NULL THEN NULL\n" +
                "            ELSE\n" +
                "                (SELECT P.NAME\n" +
                "                 FROM MAS_PARTY P,\n" +
                "                      MAS_PARTY_ROLE R\n" +
                "                 WHERE P.ID = R.PARTY_ID\n" +
                "                   AND R.ID = A.FOLLOW_ID) END)       SETTLE_PERSON\n" +
                "               FROM WAR_USED_CAR A\n" +
                "              WHERE A.STATE = 'approve' " +
                "  AND trunc(A.DATE_APPROVE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND A.DEPT_ID=:DEPT_ID");
        }
    }

    /**
     * 二手车佣金月SQL
     * @param param
     * @param stringBuffer
     */
    public void usedCarMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(USER_CAR_FEE) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }

    /**
     * 跨公司结算sql
     *
     * @param param
     * @param stringBuffer
     */
    public void crossSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT A.RETAIL_ORDER_ID                                               ID,\n" +
                "       'Cross'                                                         INNER_BUSINESS_TYPE,\n" +
                "       (case when type = 'in' then '会员跨公司消费-收入' else '会员跨公司消费-支出' end) INCOME_TYPE,\n" +
                "       TO_CHAR(A.STATISTIC_DATE, 'YYYY-MM-DD HH24:MI:SS')              SETTLE_DATE,\n" +
                "       A.COMPANY_ID                                                    GSID,\n" +
                "       A.TYPE                                                    TYPE,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.COMPANY_ID)                                     GSMC,\n" +
                "       A.PARTNER_COMPANY_ID                                            SSGSID,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.PARTNER_COMPANY_ID)                             SSGSMC,\n" +
                "       A.SETTLEMENT_AMOUNT TOTAL,\n" +
                "       A.CAR_LICENSE                                                   CAR_LICENSE\n" +
                " FROM vip_union_settle A\n" +
                " WHERE trunc(A.STATISTIC_DATE) = :SETTLE_DATE");
        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND A.DEPT_ID=:DEPT_ID");
        }
    }

    /**
     * 跨公司结算月SQL
     * @param param
     * @param stringBuffer
     */
    public void crossMonthSql(JSONObject param, StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(CROSS_COMPANY_INCOME+CROSS_COMPANY_PAY) TOTAL ,TO_CHAR(DATE_OF_STATISTICS , 'YYYY-MM-DD')  DATE_OF_STATISTICS\n" +
                "FROM RCL_DAILYSALESREPORT WHERE DATE_OF_STATISTICS >=:FIRST_DAY  AND DATE_OF_STATISTICS<= :LAST_DAY");

        if (param.containsKey("DEPT_ID")) {
            stringBuffer.append("  AND DEPT_ID=:DEPT_ID");
        }
        stringBuffer.append(" GROUP BY DATE_OF_STATISTICS ORDER BY DATE_OF_STATISTICS desc ");
    }


    /**
     * 零售具体单据SQL
     * @param stringBuffer
     */
    public String retailOrderSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT U.ID,\n" +
                "       M.NAME                             PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_UOM_QTY,\n" +
                "       I.PRICE_TOTAL,\n" +
                "       I.DISCOUNT_AMOUNT,\n" +
                "       TO_CHAR(U.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       (SELECT SUM(A.DISCOUNT_AMOUNT)\n" +
                "        FROM RETAIL_ORDER_DISCOUNT A\n" +
                "        WHERE A.RETAIL_ITEM_ID = I.ID\n" +
                "          AND A.DISCOUNT_TYPE = '001598') RED_ENVELOPE_CASH_BACK,\n" +
                "       (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C,\n" +
                "             MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "          AND D.ID = I.MAIN_SALESMAN)     SALESMAN_NAME,\n" +
                "       (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C,\n" +
                "             MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "          AND D.ID = I.SUB_SALESMAN)      SUB_SALESMAN\n" +
                "\n" +
                "FROM RETAIL_ORDER_ITEM I\n" +
                "         LEFT JOIN MAS_PRODUCT M ON M.ID = I.PRODUCT_ID,\n" +
                "     RETAIL_ORDER U\n" +
                "WHERE U.ID = I.SALE_ID AND U.ID=:ID");
        return stringBuffer.toString();
    }


    /**
     * 零售单据收款汇总SQL
     * @param stringBuffer
     */
    public String retailOrderPayMethodSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(NVL(B.AMOUNT, 0))                                                        TOTAL,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "     RETAIL_ORDER C\n" +
                "WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "  AND C.ID = :ID \n" +
                "GROUP BY B.PAYMENT_METHOD_TYPE");
        return stringBuffer.toString();
    }

    /**
     * 分销具体单据SQL
     * @param stringBuffer
     */
    public String saleOrderSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT U.ID,\n" +
                "       M.NAME                             PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_UOM_QTY,\n" +
                "       TO_CHAR(U.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       I.PRICE_TOTAL\n" +
                "\n" +
                "FROM SALE_ORDER_ITEM I\n" +
                "         LEFT JOIN MAS_PRODUCT M ON M.ID = I.PRODUCT_ID,\n" +
                "     SALE_ORDER U\n" +
                "WHERE U.ID = I.SALE_ID AND U.ID=:ID");
        return stringBuffer.toString();
    }

    /**
     * 分销退货具体单据SQL
     * @param stringBuffer
     */
    public String saleOrderReturnSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT U.ID,\n" +
                "       M.NAME                             PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_UOM_QTY,\n" +
                "       TO_CHAR(U.DATE_APPROVE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "       I.PRICE_TOTAL\n" +
                "\n" +
                "FROM SALE_RETURN_ITEM I\n" +
                "         LEFT JOIN MAS_PRODUCT M ON M.ID = I.PRODUCT_ID,\n" +
                "     SALE_RETURN U\n" +
                "WHERE U.ID = I.SALE_RETURN_ID AND U.ID=:ID");
        return stringBuffer.toString();
    }

    /**
     * 内协退货凭证具体单据SQL
     * @param stringBuffer
     */
    public String voucherReturnSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT U.ID,\n" +
                "       M.NAME                             PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_QTY PRODUCT_UOM_QTY,\n" +
                "         TO_CHAR(U.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +

                "       I.PRICE_TOTAL\n" +
                "\n" +
                "FROM ACC_VOUCHER_ITEM I\n" +
                "         LEFT JOIN MAS_PRODUCT M ON M.ID = I.PRODUCT_ID,\n" +
                "     ACC_VOUCHER U\n" +
                "WHERE U.ID = I.VOUCHER_ID AND U.ID=:ID");
        return stringBuffer.toString();
    }

    /**
     * 接案介绍费具体单据SQL
     * @param stringBuffer
     */
    public String introductionFeeSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT\n" +
                "       TO_CHAR(U.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "        case when U.EXPENSE_TYPE='infoExpense' then '接案介绍费'\n" +
                "                     when U.EXPENSE_TYPE='platformExpense' then '平台管理费' else '定损管理费' end as EXPENSE_TYPE,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = U.DEPT_ID) DEPT_NAME,\n" +
                "       U.ID,\n" +
                "       NVL(U.AMOUNT, 0) PRICE_TOTAL\n" +
                "FROM FN_SETTLE_EXPENSE U\n" +
                "WHERE U.EXPENSE_TYPE = 'infoExpense'\n" +
                "  AND U.TYPE = 'in'\n" +
                "AND  U.ID=:ID");
        return stringBuffer.toString();
    }

    /**
     * 跨公司结算具体单据SQL
     * @param stringBuffer
     */
    public String crossSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT\n" +
                "           A.RETAIL_ORDER_ID ID,\n" +
                "           TO_CHAR(A.RECEIPT_DATE, 'YYYY-MM-DD HH24:MI:SS') SETTLE_DATE,\n" +
                "           A.COMPANY_ID GSID,\n" +
                "           (SELECT NAME FROM MAS_PARTY A, MAS_PARTY_ROLE P WHERE A.ID = P.PARTY_ID AND P.ID = A.COMPANY_ID) GSMC,\n" +
                "           A.PARTNER_COMPANY_ID SSGSID,\n" +
                "           (SELECT NAME FROM MAS_PARTY A, MAS_PARTY_ROLE P WHERE A.ID = P.PARTY_ID AND P.ID = A.PARTNER_COMPANY_ID) SSGSMC,\n" +
                "           (select c.NAME from MAS_PRODUCT c where c.ID = b.PRODUCT_ID)PRODUCT_NAME,\n" +
                "           b.SALE_PRICE XSDJ,\n" +
                "           (case when vip_balance_type = '3' then b.AMOUNT_USED else 0 end ) DKJE,\n" +
                "           (case when vip_balance_type = '3' then b.SETTLEMENT_AMOUNT else 0 end ) DKJSJE,\n" +
                "           0 FXJE,\n" +
                "           0 FXJSJE,\n" +
                "           (case when b.vip_balance_type = '2' then 1 else 0 end ) DHQSL,\n" +
                "           (case when vip_balance_type = '2' then b.AMOUNT_USED else 0 end ) DHQDKJE,\n" +
                "           (case when vip_balance_type = '2' then b.SETTLEMENT_AMOUNT else 0 end ) DHQJSJE,\n" +
                "           (case when vip_balance_type = '1' then b.AMOUNT_USED else 0 end ) AMOUNT_USED_BB,\n" +
                "           (case when vip_balance_type = '1' then b.SETTLEMENT_AMOUNT else 0 end ) SETTLEMENT_AMOUNT_BB,\n" +
                "           a.USE_SAVINGS SYJE,\n" +
                "           (CASE WHEN A.PAYMENT_METHOD_TYPE = 'balance' THEN a.SETTLEMENT_AMOUNT ELSE B.SETTLEMENT_AMOUNT END) PRICE_TOTAL,\n" +
                "           a.GIVE_SAVINGS  JSJE,\n" +
                "           (select d.NAME from MAS_DEPT D where d.id = a.PARTNER_DEPT_ID) PARTNER_DEPT\n" +
                "           FROM vip_union_settle A left join VIP_UNION_SETTLE_ITEM b on a.id = b.VIP_UNION_SETTLE_ID\n" +
                "   WHERE A.RETAIL_ORDER_ID = :ID " +
                "   and A.DEPT_ID = :DEPT_ID" +
                "   and A.type = :TYPE");
        return stringBuffer.toString();
    }

    /**
     * 跨公司结算收款汇总SQL
     * @param stringBuffer
     */
    public String crossPayMethodSql(StringBuffer stringBuffer) {
        stringBuffer.append("select SUM(NVL(JSJE, 0))                                                        TOTAL,\n" +
                "                     SKFSID PAYMENT_METHOD_TYPE,\n" +
                "                      SKFSMC PAYMENT_METHOD_NAME\n" +
                "from VIP_SURPLUS_SK\n" +
                "WHERE  RETAIL_ORDER_ID = :ID \n" +
                "GROUP BY SKFSID,SKFSMC");
        return stringBuffer.toString();
    }


    /**
     * 订金具体单据SQL
     * @param stringBuffer
     */
    public String depositSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT U.ID,\n" +
                "       M.NAME                             PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_UOM_QTY,\n" +
                "       I.PRICE_TOTAL\n" +
                "\n" +
                "FROM ADVANCE_PAYMENT_ITEM I\n" +
                "         LEFT JOIN MAS_PRODUCT M ON M.ID = I.PRODUCT_ID,\n" +
                "     ADVANCE_PAYMENT U\n" +
                "WHERE U.ID = I.ADVANCE_PAYMENT_ID AND U.ID=:ID");
        return stringBuffer.toString();
    }

    /**
     * 订金单据收款汇总SQL
     * @param stringBuffer
     */
    public String depositPayMethodSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(NVL(B.AMOUNT, 0))                                                        TOTAL,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME\n" +
                "\n" +
                "FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "     ADVANCE_PAYMENT U\n" +
                "WHERE U.ID = B.PAYMENT_ID\n" +
                "  AND U.ID = :ID \n" +
                "GROUP BY B.PAYMENT_METHOD_TYPE");
        return stringBuffer.toString();
    }
    /**
     * 资金收入具体单据SQL
     * @param stringBuffer
     */
    public String fundIncomeSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT M.ID,(SELECT A.NAME FROM MAS_AMT_RECEIPT A WHERE A.ID=I.AMT_RECEIPT_ID) INVOICE_TYPE,\n" +
                "       I.INCOME_AMOUNT PRICE_TOTAL FROM MONEY_INCOME_ITEM I\n" +
                "    LEFT JOIN MONEY_INCOME M ON  I.MONEY_INCOME=M.ID\n" +
                "WHERE M.ID=:ID");
        return stringBuffer.toString();
    }

    /**
     * 资金收入单据收款汇总SQL
     * @param stringBuffer
     */
    public String fundIncomePayMethodSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT SUM(NVL(B.INCOME_AMOUNT, 0))                                                        TOTAL,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       (SELECT M.NAME FROM MAS_PAYMENT_METHOD M WHERE M.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_NAME FROM MONEY_INCOME_ITEM B\n" +
                "    LEFT JOIN MONEY_INCOME U ON  B.MONEY_INCOME=U.ID\n" +
                "WHERE  U.ID = :ID \n" +
                "GROUP BY B.PAYMENT_METHOD_TYPE");
        return stringBuffer.toString();
    }

    /**
     * 二手车具体单据SQL
     * @param stringBuffer
     */
    public String usedCarSql(StringBuffer stringBuffer) {
        stringBuffer.append("SELECT U.ID,\n" +
                "       STORE_PRICE PRICE_TOTAL\n" +
                "\n" +
                "FROM WAR_USED_CAR U\n" +
                "WHERE U.ID=:ID");
        return stringBuffer.toString();
    }


    /**
     * 无收款方式汇总SQL
     * @param stringBuffer
     */
    public String notPayMethodSql(StringBuffer stringBuffer) {
        return null;
    }
}

